--1. Between '20120112' And '20120119' 
--2. Between '20120120' And '20120122' 
--3. Between '20120124' And '20120126'
--4. Between '20120127' And '20120131'
--5. Between '20120201' And '20120203'
--6. Between '20120204' And '20120210' at 04-Dec-2012
--7. Between '20120211' And '20120228' at 14-Dec-2012 D&D
--8. Between '20120301' And '20120322' 

select * from tblCEUser Where (Convert(varchar(8), CreatedDate, 112) Between '20120301' And '20120331')
And Email NOT IN ('cheryl3@teledirect.com.sg', 'cx.thailand@gmail.com', 'paemika.a@comaprexpress.com', 'sum_aoy@hotmail.com', 'roy.ebiz@gmail.com', 'kyawthant.phyo@teledirect.com.sg', 'cx.tester188@gmail.com',
'schahryar@gmail.com', 'roy.siew@comparexpress.com', 'cherylmae.lanot@teledirect.com.sg', 'paemika.a@comparexpress.com', 'arthit.a@comparexpress.com', 'nnnn@hotmail.com', 'phyokyawthant@gmail.com', 'kandasvel@gmail.com')
And (IsReject IS NULL OR IsReject = 0) And UsrTypeID = 400
And CEID NOT IN (Select CustomerID from tblCXCRMAgentLeads Where (Convert(varchar(8), CreatedDate, 112) Between '20120901' And '20130130'))
And CEID NOT IN (select RefCEUserID  from tblNonGGCarMoreDetails)
Order By CEID

--547		broker1@gluaygluay.com
--18808	broker3@gluaygluay.com
--8543	broker5@gluaygluay.com
--18813	broker6@gluaygluay.com
--8546	broker7@gluaygluay.com
--18818	broker8@comparexpress.co.th

--1395 1396 547
--1397 1400 18808
--1401 1404 8543
--1405 1406 18813
--1407 1408 8546
--1409 1410 18818

--select top 10 * from tblCXCRMAgentLeads 

--INSERT INTO tblCXCRMAgentLeads(CustomerID, CarID, AgentID, CallType, Status)
--select CEID, Max(CarID), 18818, 100, 1  from tblCEUser U
--INNER JOIN tblCarDetailTH CD ON U.CEID = CD.RefCEUserID
--Where (Convert(varchar(8), U.CreatedDate, 112) Between '20120211' And '20120228')
--And Email NOT IN ('cheryl3@teledirect.com.sg', 'cx.thailand@gmail.com', 'paemika.a@comaprexpress.com', 'sum_aoy@hotmail.com', 'roy.ebiz@gmail.com', 'kyawthant.phyo@teledirect.com.sg', 'cx.tester188@gmail.com',
--'schahryar@gmail.com', 'roy.siew@comparexpress.com', 'cherylmae.lanot@teledirect.com.sg', 'paemika.a@comparexpress.com', 'arthit.a@comparexpress.com', 'nnnn@hotmail.com', 'phyokyawthant@gmail.com', 'kandasvel@gmail.com')
--And (IsReject IS NULL OR IsReject = 0)
--And U.CEID NOT IN (Select CustomerID from tblCXCRMAgentLeads Where (Convert(varchar(8), CreatedDate, 112) Between '20120101' And '20121230'))
--And U.CEID NOT IN (Select RefCEUserID from tblCarDetailTH Where (Convert(varchar(8), CreatedDate, 112) Between '20120901' And '20121230'))
--And (U.CEID Between 1409 And 1410)
--Group By CEID

--select count(AgentLeadID), AgentID, [STATUS] from tblCXCRMAgentLeads
--Group By AgentID, [STATUS]
--HAVING [STATUS] = 1

--select top 10 * from tblNonGGCarMoreDetails 

--select top 10 * from tblNonGGTransaction Where TransactionTypeID = 1000

--select count(TransactionID) from tblNonGGTransaction Where TransactionTypeID = 1000

--select count(CECarMoreDetailID), Sum(TotalCollectedPremium), Convert(varchar(6), T.CreatedDate, 112) from tblNonGGTransaction T 
--INNER JOIN tblNonGGCarMoreDetails CM ON T.CECarID = CM.CECarID And T.RefCEUserID = CM.RefCEUserID
--Where T.TransactionTypeID = 1000
--Group By Convert(varchar(6), T.CreatedDate, 112)

	Declare @CarID bigint
	Declare @AgentID bigint
	Declare @CustomerID bigint

	SELECT TOP 1 @AgentID = CEID from tblCEUser Where CEID IN (547, 18808, 8543, 18813, 8546, 18818) Order By NewID()

	Declare @Broker1PendingCount int
	Declare @Broker8PendingCount int

	DECLARE @@tblAgentPendingLead TABLE 
	(
		AgentID bigint,
		[Status] smallint,
		[PendingCount] int
	)
	
	DECLARE @@tblTempAgentPendingLead TABLE 
	(
		AgentID bigint,
		[Status] smallint,
		[PendingCount] int
	)
	
	--Get Temp Counting (May not include all agentID)
	INSERT INTO @@tblTempAgentPendingLead (AgentID, [Status], [PendingCount])
	select l.AgentID, l.[Status], Count(l.AgentLeadID) AS [PendingCount] 
	FROM tblCXCRMAgentLeads l
	Where (l.[Status] = 1) And Convert(varchar(8), l.CreatedDate, 112) > '20121101'
	And (l.CallType = 100 OR l.CallType = 101) And l.AgentID IN (547, 18808, 8543, 18813, 8546, 18818)
	And l.CustomerID > 0
	Group By l.AgentID, l.[Status]
	
	--Create All Agent PendindCount = 0
	INSERT INTO @@tblAgentPendingLead (AgentID, [Status], [PendingCount])
	select CEID, 1, 0 From tblCEUser WHERE CEID IN (547, 18808, 8543, 18813, 8546, 18818)

	select 'Pending', l.AgentID, l.[Status], Count(l.AgentLeadID) AS [PendingCount] 
	FROM tblCXCRMAgentLeads l
	Where (l.[Status] = 1) And Convert(varchar(8), l.CreatedDate, 112) > '20121101'
	And (l.CallType = 100 OR l.CallType = 101) And l.AgentID IN (547, 18808, 8543, 18813, 8546, 18818)
	And l.CustomerID > 0
	Group By l.AgentID, l.[Status]

	select 'Call Back Due', l.AgentID, l.[Status], Count(l.AgentLeadID) AS [PendingCount] 
	FROM tblCXCRMAgentLeads l LEFT JOIN tblCXCRMCallStatus s ON l.AgentLeadID = s.AgentLeadID And l.AgentID = s.AgentID And l.CallType = s.CallTypeID
	Where (l.[Status] = 2) And Convert(varchar(8), l.CreatedDate, 112) > '20121101'
	And (l.CallType = 100 OR l.CallType = 101) And l.AgentID IN (547, 18808, 8543, 18813, 8546, 18818)
	And l.CustomerID > 0 And s.CallBackDateTime <= DateAdd(hour, 2, GetDate())
	Group By l.AgentID, l.[Status]

	select 'Follow Up', l.AgentID, l.[Status], Count(l.AgentLeadID) AS [PendingCount] 
	FROM tblCXCRMAgentLeads l
	Where (l.[Status] = 3) And Convert(varchar(8), l.CreatedDate, 112) > '20121101'
	And (l.CallType = 100 OR l.CallType = 101) And l.AgentID IN (547, 18808, 8543, 18813, 8546, 18818)
	And l.CustomerID > 0 And (l.ModifiedDate+1) <= DateAdd(hour, 2, GetDate())
	Group By l.AgentID, l.[Status]

	--Get Pending Count from Temp
	Update l SET l.PendingCount = t.PendingCount
	From @@tblTempAgentPendingLead t INNER JOIN  @@tblAgentPendingLead l ON t.AgentID = l.AgentID
	
	delete from @@tblTempAgentPendingLead
	
	INSERT INTO @@tblTempAgentPendingLead (AgentID, [Status], [PendingCount])
	select l.AgentID, l.[Status], Count(l.AgentLeadID) AS [PendingCount] 
	FROM tblCXCRMAgentLeads l LEFT JOIN tblCXCRMCallStatus s ON l.AgentLeadID = s.AgentLeadID And l.AgentID = s.AgentID And l.CallType = s.CallTypeID
	Where (l.[Status] = 2) And Convert(varchar(8), l.CreatedDate, 112) > '20121101'
	And (l.CallType = 100 OR l.CallType = 101) And l.AgentID IN (547, 18808, 8543, 18813, 8546, 18818)
	And l.CustomerID > 0 And s.CallBackDateTime <= DateAdd(hour, 2, GetDate())
	Group By l.AgentID, l.[Status]
	
	Update l SET l.PendingCount = l.PendingCount + t.PendingCount
	From @@tblTempAgentPendingLead t INNER JOIN  @@tblAgentPendingLead l ON t.AgentID = l.AgentID
	
	delete from @@tblTempAgentPendingLead
	
	INSERT INTO @@tblTempAgentPendingLead (AgentID, [Status], [PendingCount])
	select l.AgentID, l.[Status], Count(l.AgentLeadID) AS [PendingCount] 
	FROM tblCXCRMAgentLeads l
	Where (l.[Status] = 3) And Convert(varchar(8), l.CreatedDate, 112) > '20121101'
	And (l.CallType = 100 OR l.CallType = 101) And l.AgentID IN (547, 18808, 8543, 18813, 8546, 18818)
	And l.CustomerID > 0 And (l.ModifiedDate+1) <= DateAdd(hour, 2, GetDate())
	Group By l.AgentID, l.[Status]
	
	Update l SET l.PendingCount = l.PendingCount + t.PendingCount
	From @@tblTempAgentPendingLead t INNER JOIN  @@tblAgentPendingLead l ON t.AgentID = l.AgentID
	
	delete from @@tblTempAgentPendingLead
	
	--Broker 1 Pending Count
	Select @Broker1PendingCount = PendingCount from @@tblAgentPendingLead 
	WHERE AgentID = 547
	
	--Broker 8 Pending Count
	Select @Broker8PendingCount = PendingCount from @@tblAgentPendingLead 
	WHERE AgentID = 18818
	
	Select * from @@tblAgentPendingLead

	Select top 1 * FROM @@tblAgentPendingLead Where AgentID IN (18808, 8543, 18813, 8546) Order By PendingCount ASC

	Select @Broker1PendingCount Broker1PendingCount, @Broker8PendingCount Broker8PendingCount
	
	Declare curNewCustomer cursor for
	select top 60 CEID from tblCEUser Where (Convert(varchar(8), CreatedDate, 112) Between '20120301' And '20120331')
	And Email NOT IN ('cheryl3@teledirect.com.sg', 'cx.thailand@gmail.com', 'paemika.a@comaprexpress.com', 'sum_aoy@hotmail.com', 'roy.ebiz@gmail.com', 'kyawthant.phyo@teledirect.com.sg', 'cx.tester188@gmail.com',
	'schahryar@gmail.com', 'roy.siew@comparexpress.com', 'cherylmae.lanot@teledirect.com.sg', 'paemika.a@comparexpress.com', 'arthit.a@comparexpress.com', 'nnnn@hotmail.com', 'phyokyawthant@gmail.com', 'kandasvel@gmail.com')
	And (IsReject IS NULL OR IsReject = 0) And UsrTypeID = 400
	And CEID NOT IN (Select CustomerID from tblCXCRMAgentLeads Where (Convert(varchar(8), CreatedDate, 112) Between '20120901' And '20130131'))
	And CEID NOT IN (select RefCEUserID  from tblNonGGCarMoreDetails)
	Order By CEID

	OPEN curNewCustomer 
	FETCH NEXT FROM curNewCustomer INTO @CustomerID
	WHILE (@@fetch_status <> -1)
	BEGIN	

		Select top 1 @CarID = c.CarId from tblCarDetailTH c
		INNER JOIN tblCarPolicy cp ON c.CarID = cp.CECarID And c.RefCEUserID = cp.RefCEUserID
		Where c.RefCEUserId = @CustomerID 
		Order By CarId DESC
		
		IF(@CustomerID IS NOT NULL AND @CarID IS NOT NULL)
		BEGIN
			--IF @AgentID = 547 BEGIN SET @AgentID = 18808 END 
			--ELSE IF @AgentID = 18808 BEGIN SET @AgentID = 8543 END
			--ELSE IF @AgentID = 8543 BEGIN SET @AgentID = 18813 END
			--ELSE IF @AgentID = 18813 BEGIN SET @AgentID = 8546 END
			--ELSE IF @AgentID = 8546 BEGIN SET @AgentID = 18818 END
			--ELSE IF @AgentID = 18818 BEGIN SET @AgentID = 547 END
			
			Select TOP 1 @AgentID = AgentID FROM @@tblAgentPendingLead Where AgentID IN (547, 18818, 18808, 8543, 18813, 8546) 
			Order By PendingCount ASC
				
			INSERT INTO tblCXCRMAgentLeads(CarID, AgentID, CustomerID, CallType, Status, IsFromService)
			Select CarID, @AgentID, RefCEUserID, 100, 1, 0 from tblCarDetailTH Where RefCEUserId = @CustomerID And CarId = @CarID
			
			Update @@tblAgentPendingLead SET PendingCount = PendingCount + 1
			Where AgentID = @AgentID
			
		END
	FETCH NEXT FROM curNewCustomer INTO @CustomerID
	
	END
	
	CLOSE curNewCustomer
	DEALLOCATE curNewCustomer
	
	select * from @@tblAgentPendingLead
	
	Select Count(AgentLeadID), AgentID from tblCXCRMAgentLeads
	Where Convert(varchar(8), CreatedDate, 112) = '20130116' And IsFromService = 0
	Group By AgentID
	
	